package com.aspire.core;

import java.lang.reflect.InvocationTargetException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import org.apache.commons.configuration.ConfigurationException;
import org.apache.commons.configuration.PropertiesConfiguration;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.lang.StringUtils;
import org.apache.commons.lang3.ArrayUtils;

import com.aspire.core.match.ua.object.Device;
import com.aspire.core.match.ua.object.DeviceBrand;
import com.aspire.core.match.ua.object.DeviceGWUA;
import com.aspire.core.match.ua.object.DeviceKeyword;
import com.aspire.core.match.ua.object.DeviceOS;
import com.aspire.core.match.ua.object.DeviceUA;
import com.aspire.core.match.ua.object.FilterDevice;
import com.aspire.core.match.ua.object.Series;
import com.aspirecn.core.ShareCommon;

import junit.framework.Test;
import junit.framework.TestCase;
import junit.framework.TestSuite;

public class ImportJsonToDB extends TestCase {
	/**
	 * Create the test case
	 * 
	 * @param testName
	 *            name of the test case
	 */
	public ImportJsonToDB(String testName) {
		super(testName);
	}

	/**
	 * @return the suite of tests being tested
	 */
	public static Test suite() {
		return new TestSuite(ImportJsonToDB.class);
	}

	/**
	 * Rigourous Test :-)
	 * 
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 * @throws ConfigurationException
	 * @throws NoSuchMethodException 
	 * @throws InvocationTargetException 
	 * @throws InstantiationException 
	 * @throws IllegalAccessException 
	 */
	public void testApp() throws ConfigurationException,
			ClassNotFoundException, SQLException, IllegalAccessException, InstantiationException, InvocationTargetException, NoSuchMethodException {

		toDB();
	}

	// 已经生成的json导入数据库
	private void toDB() throws ConfigurationException, ClassNotFoundException,
			SQLException, IllegalAccessException, InstantiationException, InvocationTargetException, NoSuchMethodException {
		
		String table_pre = "tl_";//表前缀，临时别的话就的改了，带.
		
		Connection conn = this.getConnection();
		QueryRunner qr = new QueryRunner();
		String sql = "";
		Object[][] objs = null;
		
		// 导入品牌
		List<DeviceBrand> deviceBrandList = ShareCommon.getInstance()
				.getUACache().getDeviceBrandList();

		System.out.println("t_device_brand:" + deviceBrandList.size());
		sql = "insert into " + table_pre + "t_device_brand(brand_id,brand_name,brand_desc,english_brand_name)"
				+ " values(?,?,?,?)";

		objs = null;
		for (DeviceBrand deviceBrand : deviceBrandList) {

			objs = ArrayUtils
					.add(objs,
							new Object[] {
									deviceBrand.getBrand_id(),
									StringUtils.trimToEmpty(deviceBrand
											.getBrand_name()),
									StringUtils.trimToEmpty(deviceBrand
											.getBrand_desc()),
									StringUtils.trimToEmpty(deviceBrand
											.getEnglish_brand_name()) });

		}

		qr.batch(conn, sql, objs);// 批量提交

		// 导入设备
		List<Device> deviceList = ShareCommon.getInstance().getUACache()
				.getDeviceList();
		System.out.println("t_device:" + deviceList.size());

		objs = null;
		sql = "insert into " + table_pre + "t_device(device_id,device_name,device_desc,device_first_alpha,brand_id,english_device_name,"
				+ "english_device_desc,os_id,screensize,preview_pic_www,series_id,os_detail,is_touch_screen)"
				+ " values(?,?,?,?,?,?,?,?,?,?,?,?,?)";
		for (Device device : deviceList) {

			objs = ArrayUtils.add(
					objs,
					new Object[] { device.getDevice_id(),
							device.getDevice_name(), device.getDevice_desc(),
							device.getDevice_first_alpha(),
							device.getBrand_id(),
							device.getEnglish_device_name(),
							device.getEnglish_device_desc(), device.getOs_id(),
							device.getScreensize(), device.getPreviewPicwww(),
							device.getSeries_id(), device.getOs_detail(),
							"null" });
		}

		qr.batch(conn, sql, objs);// 批量提交

		// getDeviceGWUAList
		List<DeviceGWUA> deviceGWUAList = ShareCommon.getInstance()
				.getUACache().getDeviceGWUAList();
		System.out.println("t_device_gw_ua:" + deviceGWUAList.size());
		sql = "insert into " + table_pre + "t_device_gw_ua(device_id,device_gw_ua)"
				+ " values(?,?)";
		objs = null;
		for (DeviceGWUA deviceGWUA : deviceGWUAList) {

			objs = ArrayUtils.add(
					objs,
					new Object[] { deviceGWUA.getDevice_id(),
							deviceGWUA.getDevice_gw_ua() });
		}
		qr.batch(conn, sql, objs);// 批量提交

		//getDeviceKeywordList
		List<DeviceKeyword> deviceKeywordList = ShareCommon.getInstance()
				.getUACache().getDeviceKeywordList();
		System.out.println("t_device_keyword:" + deviceKeywordList.size());
		sql = "insert into " + table_pre + "t_device_keyword(device_id,keyword1,keyword2,keyword3)"
				+ " values(?,?,?,?)";
		objs = null;
		for (DeviceKeyword deviceKeyword : deviceKeywordList) {

			objs = ArrayUtils.add(
					objs,
					new Object[] { 
							deviceKeyword.getDevice_id(),
							deviceKeyword.getKeyword1(),
							deviceKeyword.getKeyword2(),
							deviceKeyword.getKeyword3()
					});
		}
		qr.batch(conn, sql, objs);// 批量提交
		
		

		//mm适配的
		Map<Integer, FilterDevice> filterDeviceMap = ShareCommon.getInstance()
				.getUACache().getFilterDeviceMap();
		System.out.println("mm_deviceid:" + filterDeviceMap.size());
		sql = "insert into " + table_pre + "mm_deviceid(fulldeviceid)"
				+ " values(?)";
		objs = null;
		
		if( !filterDeviceMap.isEmpty() )
		{
		Iterator<Entry<Integer, FilterDevice>> ite = filterDeviceMap.entrySet().iterator();
		while( ite.hasNext() )
		{
			FilterDevice filterDevice = ite.next().getValue();
			objs = ArrayUtils.add(
					objs,
					new Object[] { 
							filterDevice.getFulldeviceid()
					});
		}
		qr.batch(conn, sql, objs);// 批量提交	
		}

		//系列的
		Map<Integer, Series> seriesMap = ShareCommon.getInstance()
				.getUACache().getSeriesMap();
		System.out.println("t_device_series:" + seriesMap.size());
		sql = "insert into " + table_pre + "t_device_series(series_id,series_os_id,eng_series_desc)"
				+ " values(?,?,?)";
		objs = null;
		
		if( !seriesMap.isEmpty() )
		{
		Iterator<Entry<Integer, Series>> ite1 = seriesMap.entrySet().iterator();
		while( ite1.hasNext() )
		{
			Series series = ite1.next().getValue();
			objs = ArrayUtils.add(
					objs,
					new Object[] { 
							series.getSeries_id(),
							series.getSeries_os_id(),
							series.getEng_series_desc()
					});
		}
		qr.batch(conn, sql, objs);// 批量提交
		}
		
		Map<Integer, DeviceOS> deviceOSMap = ShareCommon.getInstance()
				.getUACache().getDeviceOSMap();
		System.out.println("t_device_os:" + deviceOSMap.size());
		sql = "insert into " + table_pre + "t_device_os(os_id,osname,osvendor)"
				+ " values(?,?,?)";
		objs = null;
		Iterator<Entry<Integer, DeviceOS>> ite2 = deviceOSMap.entrySet().iterator();
		while( ite2.hasNext() )
		{
			DeviceOS deviceOS = ite2.next().getValue();
			objs = ArrayUtils.add(
					objs,
					new Object[] { 
							deviceOS.getOs_id(),
							deviceOS.getOsname(),
							deviceOS.getOsvendor()
					});
		}
		qr.batch(conn, sql, objs);// 批量提交
		
		Map<Integer, List<DeviceUA>> deviceUAMap = ShareCommon.getInstance()
				.getUACache().getDeviceUAMap();
		System.out.println("t_device_ua:" + deviceUAMap.size());
		sql = "insert into " + table_pre + "t_device_ua(device_id,device_ua,device_ua_header,device_os_ua)"
				+ " values(?,?,?,?)";
		objs = null;
		Iterator<Entry<Integer, List<DeviceUA>>> ite3 = deviceUAMap.entrySet().iterator();
		while( ite3.hasNext() )
		{
			List<DeviceUA> deviceUAList = ite3.next().getValue();
			
			for( DeviceUA deviceUA:deviceUAList )
			{
			objs = ArrayUtils.add(
					objs,
					new Object[] { 
							deviceUA.getDevice_id(),deviceUA.getDevice_ua(),deviceUA.getDevice_ua_header(),
							deviceUA.getDevice_os_ua()
					});
			}
		}
		qr.batch(conn, sql, objs);// 批量提交		
		
		DbUtils.close(conn);
	}

	private Connection m_conn = null;

	public Connection getConnection() throws ClassNotFoundException,
			ConfigurationException, SQLException {
		if (m_conn == null) {

			PropertiesConfiguration config = new PropertiesConfiguration(
					"database.properties");

			Class.forName(config.getString("db.driver"));
			Connection conn = DriverManager.getConnection(
					config.getString("db.connUrl"),
					config.getString("db.username"),
					config.getString("db.password"));
			m_conn = conn;
		}

		return m_conn;
	}
}
